<?php
namespace app\shops\controller;


use ZipArchive;
use app\shops\controller\QRcode as QRcode;
use think\Session;


/**
 * 学生管理
 */
class Student extends Base
{
    /**
     * @throws
     * 学生列表
     * @return [type] [description]
     */
    public function index()
    {
        // 获取配置信息
        $conf = config();
        // 分页信息
        $pageSize   = input('limit')    ? input('limit')    : $conf['paginate']['list_rows'];

        // 获取前台传过来的姓名查询条件
        $name = input('get.keywords') ? : "";
        $this->assign('name',$name);
        $pageParam['query']['name'] = $name;

        // 获取前台传过来的门店查询条件
        $store_id = input('get.keywords1') ? : "";
        $this->assign('keywords1',$store_id);
        $pageParam['query']['store_id'] = $store_id;

        // 列表显示所有学生
        $stu_list = Db('student') ->where('shop_id',$this->shop_id)
            ->field('store_id,stu_id,stu_no,name,sex,stu_tel,par_tel,is_finish,is_arrange')
            ->where('name','like',"%{$name}%")
            ->where('store_id','like',"%{$store_id}%")
            ->order("stu_no desc")
            ->paginate(['query'=>request()->param(),$pageSize]);

        $stu_list1=$stu_list->all();

        foreach ($stu_list1 as $k=>$v){
            $store_id = $v['store_id'];
            if ($store_id==0){
                $stu_list1[$k]['store_name'] = "未选择";
            }else{
                $stu_list1[$k]['store_name'] = Db('store')->where('store_id',$store_id)->value('store_name');
            }
        }



        // 获取门店信息列表
        $store_list = Db('store')->where('shop_id',$this->shop_id)->field('store_id, store_name')->select();

        $this->assign([
            'total'         => $stu_list->total(),
            'page'          => $stu_list->render(),
            'stu_list'      => $stu_list1,
            'stu_name'      => $name,
            'store_list'    => $store_list
        ]);

        return $this->fetch();

    }

    /**
     * 添加学生
     */
    public function add()
    {

        // 新增学生信息
        if(request()->isAjax()){

            $data = [
                'shop_id'       =>$this->shop_id,
                'name'          => input('name'),
                'sex'           => input('sex'),
                'age'           => input('age'),
                'stu_tel'       => input('stu_tel'),
                'par_tel'       => input('par_tel'),
                'store_id'      => input('store_id'),
                'create_time'   => time(),
            ];

            //对学号进行数据处理（学号=年份+商店编号+门店编号+（1000+今年已有多少学生数+1）+随机生成2位数）
            //年份
            $year = date("Y");
            //商店编号
            $shop_info = $this->shop_info($this->shop_id);
            $shop_no = $shop_info['shop_no'];
            //门店编号
            $store_no = Db('store')->where('shop_id',$this->shop_id)->where('store_id',$data['store_id'])->value('store_no');
            //1000+今年已有多少学生数+1
            $counts = Db("student")->where('store_id',$data['store_id'])->count();
            $count = ++$counts+1000;
            //随机生成2位数
            $rand = rand(10,99);
            //数据拼接
            $data['stu_no']=$year.$shop_no.$store_no.$count.$rand." ";
            $res= Db("student")->insert($data);
            if ($res){
                $this->success('添加成功');
            }else{
                $this->error('网络异常');
            }
        }


        // 获取门店信息列表
        $store_list = Db('store')->where('shop_id',$this->shop_id)->field('store_id, store_name')->select();

        // 获取班级信息列表
        $classes_list = Db('classes')->where('shop_id',$this->shop_id)->field('id, class_name')->select();

        $this->assign([
            'store_list'=>$store_list,
            'classes_list'=>$classes_list
        ]);

        return $this->fetch();
    }


    /**
     * @throws
     * 编辑学生信息
     */
    public function edit()
    {
        $stu_id = input('stu_id');
        // 更新数据
        if(request()->isAjax()){

            input('stu_id')          == null ||  $data['stu_id']       = input('stu_id');
            input('name')            == null ||  $data['name']         = input('name');
            input('sex')             == null ||  $data['sex']          = input('sex');
            input('stu_tel')         == null ||  $data['stu_tel']      = input('stu_tel');
            input('par_tel')         == null ||  $data['par_tel']      = input('par_tel');
            input('store_id')        == null ||  $data['store_id']     = input('store_id');
            input('is_finish')       == null ||  $data['is_finish']    = input('is_finish');

            $res = Db('student')->where('shop_id',$this->shop_id)->where('stu_id',$stu_id)->update($data);
            if($res===false){

                $this->error("编辑失败");
            }
            $this->success("编辑成功");
        }


        //拿到门店列表
        $store_list = Db('store')->field('store_name,store_id')->where('shop_id',$this->shop_id)->select();

        //拿到该学生相关的信息
        $stu_info = Db("student")->field('stu_id, name, sex, stu_tel, par_tel, sex, store_id, is_finish')
            ->where('shop_id',$this->shop_id)->where('stu_id',$stu_id)->find();

        $this->assign([
            'store_list'      => $store_list,
            'stu_info'        => $stu_info,
            'stu_id'          => $stu_id
        ]);

        return $this->fetch();
    }


    /**
     * 绑定该学生的id
     */
    public function bind(){

        //暂存该学生的id
        if (request()->isAjax()){
            Session::set('stu_id',input('id'));
            if (Session::get('stu_id')){
                $this->success();
            }
            $this->error();
        }

        $this->assign('stu_id',Session::get('stu_id'));
        $this->fetch('index');
    }


    /**
     * 学生绑定二维码
     */
    public function qr()
    {
        // 学生id
        $stu_id = Session::get('stu_id');

        if(!$stu_id){
            $this->error("参数不完整");
        }

        // 查询学生在哪一个门店
        $student_info = Db("student")->where('shop_id',$this->shop_id)->where('stu_id',$stu_id)->field('stu_no')->find();

        //二维码链接
        $url_arr = $student_info['stu_no'];

        $this->assign('url_arr',$url_arr);

        return $this->fetch();
    }


    /**
     * 生成并存储二维码图片
     */
    public function qrcode()
    {

        $students = Db('student')->where('shop_id',$this->shop_id)->field('stu_no,name')->select();

        foreach ($students as $k=>$v){
            $url_arr = $v['stu_no'];
            $path[] = $this->getQRcode($url_arr, 'upload/student_qrcode/'.$this->shop_id, $v['stu_no']."_".$v['name']);//获取标准二维码格式
        }

        //压缩文件并下载
        $this->zip_download($path,$this->shop_name);
        $this->success('导出成功');

    }



    /**
     * 获取标准二维码格式
     */
    public function getQRcode($url, $path, $qrcode_name){

        //判断如果文件夹不存在，建立文件夹
        if (!is_dir($path)) {
            $mode = intval('0777', 8);
            mkdir($path, $mode, true);
            chmod($path, $mode);
        }

        //拼接生成二维码图片的url地址
        $path = $path . '/' . $qrcode_name . '.png';


        //判断如果二维码图片存在，删除二维码图片
        if (file_exists($path)) {
            unlink($path);
        }

        //转换编码
        $path = iconv('UTF-8', 'GB2312//IGNORE', $path);

        //图片生成
        QRcode::png($url, ROOT_PATH.$path, '', 4, 2);

        return $path;
    }

    /**
     *  压缩文件并下载二维码图片
     */

    public function zip_download($path,$filename){
        $filename = iconv('UTF-8', 'GB2312//IGNORE', $filename);
        // 最终生成的文件名（含路径）
        $filename = ROOT_PATH.$filename.".zip";
        // 如果存在压缩文件，删除
        if(file_exists($filename)){
            unlink($filename);
        }
        //重新生成文件
        $zip = new ZipArchive();

        if($zip->open($filename , ZipArchive::CREATE) !== TRUE){
            $this->error('无法打开文件，或者文件创建失败');
        }

        // 要压缩的文件
        $datalist = $path;

        // 遍历添加待压缩文件
        foreach($datalist as $val){
            if(file_exists($val)){
                $zip->addFile($val);
            }
        }

        // 关闭
        $zip->close();

        if(!file_exists($filename)){
            // 即使创建，仍有可能失败
            $this->error('无法找到文件');
        }

        header('Content-type: application/zip');
        header('Content-Disposition: attachment; filename="download.zip"');
        readfile($filename);
    }

    /**
     * 文件导出
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function excelExport(){
        $fileName='stu_msg';
        $headArr=['学号','姓名','所在店铺','所在门店','年龄','性别','学生电话','家长电话','是否选课','是否毕业'];

        $data= Db('student')->alias('s')
            ->join('shops sh','s.shop_id=sh.shop_id')
            ->join('store sr','s.store_id=sr.store_id','left')
            ->field('s.stu_no,name,sh.shop_name,sr.store_name,age,sex,stu_tel,par_tel,is_arrange,is_finish')->select();

        //数据处理
        foreach ($data as $k=>$v){
            if ($v['sex']==0){
                $data[$k]['sex']="保密";
            }elseif ($v['sex']==1){
                $data[$k]['sex']="男";
            }else{
                $data[$k]['sex']="女";
            }
            if ($v['is_arrange']==0){
                $data[$k]['is_arrange']="未选课";
            }else{
                $data[$k]['is_arrange']="已选课";
            }
            if ($v['is_finish']==0){
                $data[$k]['is_finish']="学习中";
            }else{
                $data[$k]['is_finish']="已毕业";
            }
            if ($v['store_name']==null){
                $data[$k]['store_name']="未选择";
            }
        }
        $fileName .= ":" . date("Y-m-d", time()) . ".xls";//文件名称
        vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->getProperties();
        $key = ord("A");
        foreach ($headArr as $v) {
            $colum = chr($key);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
            $key += 1;

        }
        $column = 2;
        $objActSheet = $objPHPExcel->getActiveSheet();

        foreach ($data as $key => $rows) { // 行写入

            $span = ord("A");

            foreach ($rows as $keyName => $value) { // 列写入

                $objActSheet->setCellValue(chr($span) . $column, $value);

                $span++;

            }

            $column++;

        }
        $fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表

        $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表

        header('Content-Type: application/vnd.ms-excel');

        header("Content-Disposition: attachment;filename='$fileName'");

        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $objWriter->save('php://output'); // 文件通过浏览器下载

        exit();
    }


    /**
     * excel表格导入
     * @throws
     *
     */
    function excelToArray()
    {
        $file = request()->file('excel');
        //上传的时候的原文件名
       // $filename = $file->getInfo()['name'];
        $dir = ROOT_PATH . 'public' . DS . 'excel';
        if (!is_dir($dir.DS.date('Ymd'))) {
            $mode = intval('0777', 8);
            mkdir($dir.DS.date('Ymd'), $mode, true);
        }

        if($file){
            $info = $file->validate(['ext'=>'xlsx,xls,csv'])->move($dir);
         //   $info = $file->validate(['ext'=>'xlsx,xls,csv'])->move(ROOT_PATH . 'public' . DS . 'excel');

            if ($info){
                $path = $info->getSaveName();

                $filename = iconv('UTF-8', 'GB2312//IGNORE', $path);
                // 最终生成的文件名（含路径）
                $filename = ROOT_PATH.'public\\' .'excel\\' .$filename;

                //加载excel文件
                vendor("PHPExcel.PHPExcel");
                $objPHPExcelReader = \PHPExcel_IOFactory::load($filename);

                $sheet = $objPHPExcelReader->getSheet(0);
                $highestRow = $sheet->getHighestRow();
                $highestColumn = $sheet->getHighestColumn();

                $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M', 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
                // 一次读取一列
                $res_arr = array();
                for ($row = 2; $row <= $highestRow; $row++) {
                    $row_arr = array();
                    for ($column = 0;  ord($arr[$column]) <= ord($highestColumn); $column++) {
                        $val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
                        $row_arr[] = $val;
                    }

                    $res_arr[] = $row_arr;
                }

                $store_id = input('store_id');

                //对学号进行数据处理（学号=年份+商店编号+门店编号+（1000+今年已有多少学生数+1）+随机生成2位数）
                //年份
                $year = date("Y");
                //商店编号
                $shop_info = $this->shop_info($this->shop_id);
                $shop_no = $shop_info['shop_no'];
                //门店编号
                $store_no = Db('store')->where('shop_id',$this->shop_id)->where('store_id',$store_id)->value('store_no');
                //1000+今年已有多少学生数+1
                $counts = Db("student")->where('store_id',$store_id)->count();
                $count = ++$counts+1000;

                foreach ($res_arr as $k=>$v){
                    $data[$k]['name'] = $v[0];
                    $data[$k]['age']  = $v[1];
                    if ($v[2]=='男'){
                        $data[$k]['sex'] = 1;
                    }elseif ($v[2]=='女'){
                        $data[$k]['sex'] = 2;
                    }else{
                        $data[$k]['sex'] = 0;
                    }
                    $data[$k]['stu_tel'] = $v[3];
                    $data[$k]['par_tel'] = $v[4];
                    $data[$k]['store_id'] = $store_id;
                    $data[$k]['shop_id'] = $this->shop_id;
                    //随机生成2位数
                    $rand = rand(10,99);
                    $count = $count+$k;
                    $data[$k]['stu_no']  = $year.$shop_no.$store_no.$count.$rand." ";
                    $data[$k]['create_time']=time();

                }
                $res = Db('student')->insertAll($data);
                if ($res){
                    $this->success('成功');
                }else{
                    $this->error('网络异常');
                }
            }else{
                // 上传失败获取错误信息
                // return $file->getError();
                $this->error('网络异常');
            }
        }else{
            // 上传失败获取错误信息
            // return $file->getError();
            $this->error("请选择文件~");
        }


    }

}
